package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DbSalesOrder implements IFDBSalesOrder {
	
	private Connection con;
	private DbSalesLine salesLineDb;	
	private DbDelivery deliveryDb;
	private DbEmployee employeeDb;
	private DbContact contactDb;
	
	public DbSalesOrder()
	{
		con = DbConnection.getInstance().getDBcon();
		salesLineDb = new DbSalesLine();
		deliveryDb = new DbDelivery();
		employeeDb = new DbEmployee();
		contactDb = new DbContact();
	}

	@Override
	public int getNextSalesOrderId() {
		return GetMax.getMaxId("Select max(id) from SalesOrder");
	}
	
	@Override
	public ArrayList<SalesOrder> getAllSalesOrder() 
	{
		return miscWhere("");
	}

	@Override
	public SalesOrder getSalesOrderById(int salesOrderId) 
	{
		String wClause = " id = '" + salesOrderId + "'";
		return singleWhere(wClause);
	}
	
	@Override
	public ArrayList<SalesOrder> getSalesOrderByCustomerId(int customerId) {
		String wClause = " customerID = '" + customerId + "'";
		return miscWhere(wClause);
	}

	@Override
	public ArrayList<SalesOrder> getSalesOrderByEmployeeId(int employeeId) {
		String wClause = " employeeID = '" + employeeId + "'";
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<SalesOrder> getSalesOrdersByStatus(String status) {
		String wClause = " status = '" + status + "'";
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<SalesOrder> getSalesOrdersByDeliveryId(int deliveryId) {
		String wClause = " deliveryID = '" + deliveryId + "'";
		return miscWhere(wClause);
	}	

	@Override
	public int insertSalesOrder(SalesOrder salesOrder) throws Exception 
	{
		int nextID = GetMax.getMaxId("Select max(id) from SalesOrder");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);
		int rc = -1;
		
		java.sql.Date sqlCreationDate = new java.sql.Date(salesOrder.getCreationDate().getTime());
		java.sql.Date sqlPaymentDate = new java.sql.Date(salesOrder.getPaymentDate().getTime());
		
		String query="INSERT INTO SalesOrder(id, customerID, total, discount, creationDate, " +
				"paymentDate, employeeID, deliveryID, status)  VALUES('"+
				nextID  + "','"  +
				salesOrder.getCustomer().getId()  + "','"  +
				salesOrder.getTotal() + "','" +
				salesOrder.getDiscount() + "','" +
				sqlCreationDate + "','" +
				sqlPaymentDate + "','" +
				salesOrder.getEmployee().getId() + "','" +
				salesOrder.getDelivery().getDeliveryId() + "','" +
				salesOrder.getStatus().toLowerCase() + "')";

		System.out.println("insert SalesOrder: " + query);
		try{ // insert new SalesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			
			insertSalesList(salesOrder.getSalesLine(), nextID);
		}//end try
		catch(SQLException ex){
			System.out.println("SalesOrder not created");
			throw new Exception ("SalesOrder is not inserted correct");
		}
		return(rc);
	}

	private void insertSalesList(ArrayList<SalesLine> salesLineList, int salesOrderId) throws Exception {
		for(SalesLine salesLine : salesLineList)
		{
			salesLineDb.insertSalesLine(salesLine, salesOrderId);
		}
	}

	@Override
	public int updateSalesOrder(SalesOrder salesOrder) 
	{
		java.sql.Date sqlCreationDate = new java.sql.Date(salesOrder.getCreationDate().getTime());
		java.sql.Date sqlPaymentDate = new java.sql.Date(salesOrder.getPaymentDate().getTime());
		int rc=-1;
		String query="UPDATE SalesOrder SET "+
				"customerID ='" + salesOrder.getCustomer().getId() + "', " +
				"total ='" + salesOrder.getTotal() + "', " +
				"discount ='" + salesOrder.getDiscount() + "', " +
				"creationDate ='" + sqlCreationDate + "', " +
				"paymentDate ='" + sqlPaymentDate + "', " +
				"employeeID ='" + salesOrder.getEmployee().getId() + "', " +
				"deliveryID ='" + salesOrder.getDelivery().getDeliveryId() + "', " +
				"status ='" + salesOrder.getStatus().toLowerCase() + "' " +
				" WHERE id = '" + salesOrder.getId() + "'";
		
		System.out.println("Update query:" + query);
		try{ // update SalesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			
			updateSalesLine(salesOrder.getSalesLine(), salesOrder.getId());
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	private void updateSalesLine(ArrayList<SalesLine> salesLineList, int salesOrderId) throws Exception {
		salesLineDb.deleteSalesLine(salesOrderId);
		for(SalesLine salesLine : salesLineList)
		{
			salesLineDb.insertSalesLine(salesLine, salesOrderId);
		}
	}

	@Override
	public int deleteSalesOrder(SalesOrder salesOrder) 
	{
		int rc=-1;

		String query="DELETE FROM SalesOrder WHERE id = '" +
				salesOrder.getId() + "'";
		System.out.println(query);
		try{ // delete from salesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			salesLineDb.deleteSalesLine(salesOrder.getId());
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	private ArrayList<SalesOrder> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	

		String query =  buildQuery(wClause);

		try{ // read the salesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				SalesOrder salesOrderObj = new SalesOrder();
				salesOrderObj = buildSalesOrder(results);
				list.add(salesOrderObj);
			}//end while
			stmt.close();

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select SalesOrder: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	private SalesOrder singleWhere(String wClause)
	{
		ResultSet results;
		SalesOrder salesOrderObj = new SalesOrder();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the salesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				salesOrderObj = buildSalesOrder(results);
				stmt.close();
			}
			else{ //no salesOrder was found
				salesOrderObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception - SalesOrder: "+e);
		}
		return salesOrderObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query="SELECT id, creationDate, paymentDate, total, discount, customerID, " + 
				"deliveryID, employeeID, status FROM SalesOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	
	private SalesOrder buildSalesOrder(ResultSet results)
	{   SalesOrder salesOrderObj = new SalesOrder();
	try{ // the columns from the table salesOrder are used
		salesOrderObj.setId(results.getInt("id"));
		salesOrderObj.setCreationDate(results.getDate("creationDate"));
		salesOrderObj.setPaymentDate(results.getDate("paymentDate"));
		salesOrderObj.setTotal(results.getDouble("total"));
		salesOrderObj.setDiscount(results.getInt("discount"));
		salesOrderObj.setCustomer(contactDb.getCustomerById(results.getInt("customerID")));
		salesOrderObj.setDelivery(deliveryDb.getDeliveryById(results.getInt("deliveryID")));
		salesOrderObj.setEmployee(employeeDb.getEmployeeById(results.getInt("employeeID")));
		salesOrderObj.setStatus(results.getString("status"));
		salesOrderObj.setSalesLine(salesLineDb.getAllSalesLines(results.getInt("id")));
	}
	catch(Exception e)
	{
		System.out.println("error in building the SalesOrder object");
	}
	return salesOrderObj;
	}
}
